﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.Common;
using System.Diagnostics;
using Microsoft.VisualBasic.FileIO;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;
using SSISFromCode.Events;
using SSISFromCode.Helpers;

namespace SSISFromCode
{

    /// <summary>
    /// Various descendant classes from PackageGenerator use these interfaces
    /// </summary>
    internal interface IPreConfigureColumns
    {
        void ConfigureColumns();
    }

    internal interface IRequireTransform
    {
        void ConfigureDataTransform();
    }

    /// <summary>
    /// XML source component is not like the others in that it needs no source connection.
    /// </summary>
    internal interface IRequireSourceConnection
    {
        void ConfigureSourceConnection();
    }

    internal interface IRequireCleanup
    {
        void Cleanup();
    }

    /// <summary>
    /// Contains some base functionality and the base algorithm in Generate().
    /// The purpose of this class (and descendants) is to accept an input file of type XML, XLS, CSV, or fixed width and:
    /// - Determine the column names
    /// - Create a global temp table (##) in the SQL Server instance owning the connection passed into Generate() whose columns match the source data (plus a primary key column)
    /// - Map columns between the source data and the temp table (using a transformation where necessary if implementing IRequireTransform)
    /// - Import the data into the temp table
    /// Following are generated during that process and are available as properties:
    ///     - The table name of the temp table
    ///     - A list of the columns in the source data and in the temp table
    ///     - A list of messages generated by SSIS during the process.  Note that Generate returns a package.
    ///         If that package is executed, it will write to the same Messages returned by this object's Messages property
    /// </summary>
    internal abstract class PackageGenerator
    {

        protected Runtime.Package _package;

        /// <summary>
        /// This is the main pipe within the Data Flow Component.  The thing you see when you enter "Data Flow"
        /// </summary>
        protected MainPipe _pipe;

        /// <summary>
        /// The underlying data components within the data flow
        /// </summary>
        protected IDTSComponentMetaData100 _dataSource;
        protected IDTSComponentMetaData100 _dataDest;

        /// <summary>
        /// These represent the actual components you see in the designer.  The objects above create them in CreateDesignInstances().
        /// </summary>
        protected CManagedComponentWrapper _dataSourceInstance;
        protected CManagedComponentWrapper _dataDestInstance;

        /// <summary>
        /// Connections you see at the bottom of the designer
        /// </summary>
        protected Runtime.ConnectionManager _connectionSource;
        private Runtime.ConnectionManager _connectionDest;

        protected ImportOptions _options;

        /// <summary>
        /// These guys are all generated during package generation
        /// </summary>
        public string TableName { get; set; }
        public List<string> Columns { get; set; }
        public ImportMessages Messages { get; set; }

        public virtual Runtime.Package Generate( Runtime.Application app, ImportOptions options, string destinationConnectionStr, SqlConnection openConnection )
        {
            _options = options;

            ConfigureMainObjects( CreateMainObjects() );
            CreateConnections();
            ConfigureConnections( destinationConnectionStr );
            if ( this is IPreConfigureColumns )
            {
                ((IPreConfigureColumns)this).ConfigureColumns();  // flat files, xml
            }
            CreateDataFlowComponents( app );
            CreateDesignInstances();
            SetProperties( openConnection );
            ConnectDataFlowSubComponents();
            if ( this is IRequireTransform )
            {
                ( (IRequireTransform)this ).ConfigureDataTransform();  // excel, xml
            }
            MapColumns();

            //_package.Validate( _package.Connections, null, null, null );

            return _package;
        }

        private void MapColumns()
        {
            MuckWithVirtualInput();
            DoActualMapping();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <remarks>Required precursor to actual mapping I think.  That's all I know...</remarks>
        private void MuckWithVirtualInput()
        {
            /// Do stuff with the virtual input (whatever the @#$% that is)
            var virtualInput = _dataDest.InputCollection[0].GetVirtualInput();
            foreach ( IDTSVirtualInputColumn100 column in virtualInput.VirtualInputColumnCollection )
            {
                _dataDestInstance.SetUsageType( _dataDest.InputCollection[0].ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY );
            }
        }

        protected abstract void DoActualMapping();

        /// <summary>
        /// Corresponds to connecting the components in the data flow together.
        /// </summary>
        protected abstract void ConnectDataFlowSubComponents();

        /// <summary>
        /// Well, unfortunately, it sets props, and creates the table in the middle, so it's not really named well.
        /// At this point I'm reluctant to move CreateTable to it's own method after this one because order is very important herein.
        /// </summary>
        /// <param name="openConnection"></param>
        protected virtual void SetProperties( SqlConnection openConnection )
        {
            SetSourceProperties();
            ReinitializeMetadata( _dataSourceInstance );
            CreateTable( openConnection );
            SetDestProperties();
        }

        protected virtual void SetSourceProperties()
        {
            /// This thing is the source within the Data Flow Task (not the source connection).
            /// I think this junk must come after a call to CManagedComponentWrapper.ProvideComponentProperties().
            _dataSource.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface( _connectionSource );
            _dataSource.RuntimeConnectionCollection[0].ConnectionManagerID = _connectionSource.ID;
        }

        protected void SetDestProperties()
        {
            /// I know SetComponentProperty can only be called after CManagedComponentWrapper.ProvideComponentProperties()
            /// To see available component properties, open an existing package (the XML) with an existing component of that type
            _dataDestInstance.SetComponentProperty( COMPONENT_PROPERTY_NAMES.TABLE_NAME, string.Format( DESTINATION_TABLE.NAME, TableName ) );
            _dataDestInstance.SetComponentProperty( COMPONENT_PROPERTY_NAMES.TIMEOUT, 0 );
            _dataDest.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface( _connectionDest );
            _dataDest.RuntimeConnectionCollection[0].ConnectionManagerID = _connectionDest.ID;
            ReinitializeMetadata( _dataDestInstance );
        }

        protected abstract void CreateTable( SqlConnection openConnection );

        protected virtual void CreateTable( List<string> columns, SqlConnection openConnection, int columnWidth = DESTINATION_TABLE.CHAR_WIDTH )
        {
            Columns = columns;
            TableName = TableCreator.Execute( columns, openConnection, columnWidth );
        }

        protected virtual void CreateDesignInstances()
        {
            _dataSourceInstance = _dataSource.Instantiate();
            _dataSourceInstance.ProvideComponentProperties();
            _dataDestInstance = _dataDest.Instantiate();
            _dataDestInstance.ProvideComponentProperties();
        }

        protected virtual void CreateDataFlowComponents( Runtime.Application app )
        {
            CreateDataFlowSourceComponent( app );
            CreateDataFlowDestComponent( app );
        }

        private void CreateDataFlowDestComponent( Runtime.Application app )
        {
            _dataDest = _pipe.ComponentMetaDataCollection.New();
            //_dataDest.Name = "Sql Server Destination";
            _dataDest.ComponentClassID = app.PipelineComponentInfos[OBJECT_CREATION.SQL_SERVER_DEST].CreationName;
        }

        protected abstract void CreateDataFlowSourceComponent( Runtime.Application app );

        protected void CreateDataFlowSourceComponent( Runtime.Application app, string sourceType )
        {
            _dataSource = _pipe.ComponentMetaDataCollection.New();
            _dataSource.ComponentClassID = app.PipelineComponentInfos[sourceType].CreationName;
            _dataSource.ValidateExternalMetadata = false;
        }

        protected virtual void CreateConnections()
        {
            _connectionDest = _package.Connections.Add( OBJECT_CREATION.OLEDB_CONNECTION );
        }

        private void ConfigureConnections( string destinationConnectionStr )
        {
            if ( this is IRequireSourceConnection )
            {
                ((IRequireSourceConnection)this).ConfigureSourceConnection();
            }
            ConfigureDestConnection( destinationConnectionStr );
        }

        protected void ConfigureDestConnection( string destinationConnectionStr )
        {
            // This provider wouldn't work
            //_conMgrDest.ConnectionString = @"Provider=Native OLE DB\SQL Server Native Client 10.0;Data Source=.\SQLEXPRESS;Initial Catalog=FASClient;Integrated Security=True";
            _connectionDest.ConnectionString = destinationConnectionStr;
            _connectionDest.Properties[COMMON_PROPERTY_NAMES.NAME].SetValue( _connectionDest, COMPONENT_NAMES.OLEDB_CONNECTION );
            //_conMgrDest.Name = "OLE DB Connection";
            //_conMgrDest.Description = "OLE DB Connection";
            _connectionDest.Properties[OLEDB_PROPERTY_NAMES.RETAIN_SAME_CONNECTION].SetValue( _connectionDest, true );
            _connectionDest.DelayValidation = true;
        }

        /// <summary>
        /// This will cause the component to connect and update it's column metadata.
        /// It's what fires any time you click on the "Columns" section of a data flow component and any time you first open a package unless the package or component
        /// has property "DelayValidation" set to true.
        /// Except if the source connection is a flat file.  In that case it doesn't do @#$%.  That's why the FlatFileGenerator has a FlatFileColumnManager
        /// </summary>
        public static void ReinitializeMetadata( CManagedComponentWrapper component )
        {
            component.AcquireConnections( null );
            component.ReinitializeMetaData();
            component.ReleaseConnections();
        }

        /// <summary>
        /// package and main tasks
        /// </summary>
        /// <returns>The main Data Flow Task</returns>
        private Runtime.Executable CreateMainObjects()
        {
            _package = new Runtime.Package();
            return _package.Executables.Add( OBJECT_CREATION.DATA_FLOW_TASK );
        }

        private void ConfigureMainObjects( Runtime.Executable dataFlowTask )
        {
            _package.DelayValidation = true;
            _pipe = (MainPipe)( (Runtime.TaskHost)dataFlowTask ).InnerObject;
            //pipe.Events = DtsConvert.GetExtendedInterface( new ComponentEvents() as IDTSComponentEvents100 );
            if ( Messages != null )
            {
                _pipe.Events = new ComponentEvents( Messages );
            }
        }

    }

}